package crawler;
/**
 * Created by Administrator on 2015/7/4.
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.util.*;

import crawler.classes.Article;
import crawler.util.MysqlHelper;

public class ArticlesService {

	public static int saveMore(List<Article> articles, boolean isUpdate) {
		if (isUpdate) {
			return updateArticle(articles);
		} else {
			saveArticle(articles);
			return 0;
		}
	}
	static String getQuerySql() {//
		return new StringBuffer().append("select * from ")// aa
				.append("article").append(" where ")//
				.append("type = ?").append(" and key_word = ?")//
				.append(" and url = ?").append(" and title = ?")//
				//.append(" and content = ?").append(" and src_html = ?")//
				.append(" and create_time = ?").toString();//
	}
	public static int updateArticle(List<Article> articles) {
		Integer commit_num = 20;
		Connection conn = null;
		MysqlHelper mysqlHelper = new MysqlHelper();
		conn = mysqlHelper.getConn();
		try {
			conn.setAutoCommit(false);
			PreparedStatement psmt = conn.prepareStatement(getQuerySql());
			for (int i = articles.size() - 1; i >= 0; i--) {
				psmt.setString(1, articles.get(i).type);
				psmt.setString(2, articles.get(i).key_word);
				psmt.setString(3, articles.get(i).url);
				psmt.setString(4, articles.get(i).title);
				psmt.setString(5, articles.get(i).content);
				psmt.setString(6, articles.get(i).src_html);
				psmt.setString(7, articles.get(i).create_time);
				ResultSet set = psmt.executeQuery();
				set.next();
				if (set.getRow() > 0) {
					articles.remove(i);
				}
				System.out.println(set.getRow());
			}

			psmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		saveArticle(articles);
		return articles.size();

	}
	public static void saveArticle(List<Article> articles) {

		Integer commit_num = 20;
		Connection conn = null;
		MysqlHelper mysqlHelper = new MysqlHelper();
		conn = mysqlHelper.getConn();

		try {
			conn.setAutoCommit(false);
			PreparedStatement psmt = conn
					.prepareStatement("insert into article(type,key_word,url,title,content,src_html,create_time,full_content,full_html) values(?,?,?,?,?,?,?,?,?)");
			int[] result;
			for (int i = 0; i < articles.size(); i++) {
				psmt.setString(1, articles.get(i).type);
				psmt.setString(2, articles.get(i).key_word);
				psmt.setString(3, articles.get(i).url);
				psmt.setString(4, articles.get(i).title);
				psmt.setString(5, articles.get(i).content);
				psmt.setString(6, articles.get(i).src_html);
				psmt.setString(7, articles.get(i).create_time);
				psmt.setString(8, articles.get(i).full_content);
				psmt.setString(9, articles.get(i).full_html);
				psmt.addBatch();
				if ((i + 1) % commit_num == 0) {
					psmt.executeBatch();
					conn.commit();
				}
			}

			// commit_num以外的提交一次
			psmt.executeBatch();
			conn.commit();

			psmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public static void saveOne(Article article) throws ClassNotFoundException, SQLException {
		Connection conn = null;

		String url = "jdbc:mysql://localhost:3306/crawl1688?"
				+ "user=root&password=root&useUnicode=true&characterEncoding=UTF8";
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection(url);
		conn.setAutoCommit(true);

		PreparedStatement psmt_good = conn
				.prepareStatement("insert into goods(good_url,good_name,good_html,good_content_html,shop_name,shop_url) values(?,?,?,?,?,?)");
		PreparedStatement psmt_price = conn
				.prepareStatement("insert into good_price(good_url,min_num,max_num,good_price) values(?,?,?,?)");
		PreparedStatement psmt_pic = conn
				.prepareStatement("insert into good_pics(good_url,seq,smallurl,normalurl,bigurl) values(?,?,?,?,?)");
		PreparedStatement psmt_sku = conn
				.prepareStatement("insert into good_sku(good_url,good_size,good_price,inventory) values(?,?,?,?)");

		// article
		psmt_good.setString(1, article.url);
		// psmt_good.setString(3,"");
		// psmt_good.setString(4,"");
		psmt_good.addBatch();
		// good_pics

		psmt_good.executeBatch();
		psmt_pic.executeBatch();
		psmt_price.executeBatch();
		psmt_sku.executeBatch();

		psmt_good.close();
		psmt_pic.close();
		psmt_price.close();
		psmt_sku.close();
		conn.close();

		System.out.println("商品:" + article.url + " 保存成功");

	}
	// 添加商品到excel
	public static void addGoodToExcel() {

	}

	public static List<KeyWord> queryKeywords() {

		Connection conn = null;
		MysqlHelper mysqlHelper = new MysqlHelper();
		conn = mysqlHelper.getConn();
		List<KeyWord> result = new ArrayList<KeyWord>();
		try {
			// conn.setAutoCommit(false);
			PreparedStatement psmt = conn// key_words(name,create_time,update_time)
					.prepareStatement("select * from  keywords order by update_time desc");
			ResultSet resultSet = psmt.executeQuery();
			// if (resultSet.getRow() > 0) {
			result = new ArrayList<KeyWord>(resultSet.getRow());
			try {
				while (resultSet.next()) {
					String name = resultSet.getString("name");
					String create_time = resultSet.getString("create_time");
					String update_time = resultSet.getString("update_time");
					KeyWord keyword = new KeyWord(name, create_time, update_time);
					result.add(keyword);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			// }

			psmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;

	}
	public static List<SearchHistory> querySearchHistory() {

		Connection conn = null;
		MysqlHelper mysqlHelper = new MysqlHelper();
		conn = mysqlHelper.getConn();
		List<SearchHistory> result = new ArrayList<SearchHistory>();
		try {
			// conn.setAutoCommit(false);
			// article(type,key_word,url,title,content,src_html,create_time)
			// keywords(name,create_time,update_time)
			PreparedStatement psmt = conn
					.prepareStatement("select key_word,count(*) as search_count ,max(create_time) as search_time from  article group by key_word");
			ResultSet resultSet = psmt.executeQuery();
			// if (resultSet.getRow() > 0) {
			try {
				result = new ArrayList<SearchHistory>(resultSet.getRow());
				while (resultSet.next()) {
					SearchHistory item = new SearchHistory();
					item.name = resultSet.getString("key_word");
					item.search_time = resultSet.getString("search_time");
					item.search_count = resultSet.getString("search_count");

					result.add(item);
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
			// }

			psmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;

	}


	public static List<Article> queryArticles(){
		return queryArticles("");
	};
	public static List<Article> queryArticles(String keyword) {

		Connection conn = null;
		MysqlHelper mysqlHelper = new MysqlHelper();
		conn = mysqlHelper.getConn();
		List<Article> result = new ArrayList<Article>();
		try {
			// conn.setAutoCommit(false);
			String sql="";
			PreparedStatement psmt;
			if(!keyword.equals("")){
				sql="select * from  article where key_word=? order by create_time desc";
				psmt= conn// article(type,key_word,url,title,content,src_html,create_time)
						.prepareStatement(sql);
				psmt.setString(1,keyword);
				ResultSet resultSet = psmt.executeQuery();
			}else {
				sql="select * from  article  order by create_time desc";
				psmt = conn// article(type,key_word,url,title,content,src_html,create_time)
						.prepareStatement(sql);
			}

			ResultSet resultSet = psmt.executeQuery();
			// if (resultSet.getRow() > 0) {
			result = new ArrayList<Article>(resultSet.getRow());
			try {
				while (resultSet.next()) {
					String type = resultSet.getString("type");
					String key_word = resultSet.getString("key_word");
					String url = resultSet.getString("url");
					String title = resultSet.getString("title");
					String content = resultSet.getString("content");
					String src_html = resultSet.getString("src_html");
					String full_content = resultSet.getString("full_content");
					String full_html = resultSet.getString("full_html");
					String create_time = resultSet.getString("create_time");
					String qgqx = resultSet.getString("qgqx");
					String qgqd = resultSet.getString("qgqd");
					Article art =new Article(type,key_word,url,title,content,src_html,create_time,full_content,full_html,qgqx,qgqd);
					result.add(art);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			// }

			psmt.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}

	public static void deleteKeyword(String keyword) {
		Connection conn = null;
		MysqlHelper mysqlHelper = new MysqlHelper();
		conn = mysqlHelper.getConn();
		try {
			conn.setAutoCommit(false);
			PreparedStatement psmt = conn// (type,key_word,url,title,content,src_html,create_time)
					.prepareStatement("delete from keywords where  name= ?");
			psmt.setString(1, keyword);
			psmt.execute();
			psmt = conn// (type,key_word,url,title,content,src_html,create_time)
					.prepareStatement("delete from article where key_word=?;");
			psmt.setString(1, keyword);
			psmt.execute();
			
			conn.commit();
			psmt.close();
			conn.close();

		} catch (SQLException e) {
			System.out.println(keyword + e);
			e.printStackTrace();
		}
	}
	public static String getOne(String sql, String default_value) {
		String result = default_value;
		Connection conn = null;
		MysqlHelper mysqlHelper = new MysqlHelper();
		conn = mysqlHelper.getConn();
		try {
			// conn.setAutoCommit(false);
			PreparedStatement psmt = conn// (type,key_word,url,title,content,src_html,create_time)
					.prepareStatement(sql);
			ResultSet resultSet = psmt.executeQuery();
			try {
				while (resultSet.next()) {
					result = resultSet.getObject(1).toString();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			// }

			psmt.close();
			conn.close();

		} catch (SQLException e) {
			System.out.println(sql);
			e.printStackTrace();
		}

		return result;
	}

	public static int executeQuery(String sql) {
		int flag=0;
		Connection conn = null;
		MysqlHelper mysqlHelper = new MysqlHelper();
		conn = mysqlHelper.getConn();
		try {
			// conn.setAutoCommit(false);
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.execute();
			// }

			psmt.close();
			conn.close();

		} catch (SQLException e) {
			System.out.println(sql);
			e.printStackTrace();
		}
		flag=1;
		return flag;
	}



	public static Map<String, String> getKeyWordData(String keyword) {
		if(!keyword.equals("")){
			HashMap<String, String> hashMap = new HashMap<String, String>();
			String sql_total = "select count(*) from article where key_word='" + keyword + "'";
			String sql_today = "select count(*) from article where key_word='" + keyword
					+ "' and datediff(create_time,current_date)=0";
			String sql_week = "select count(*) from article where key_word='" + keyword
					+ "' and datediff(current_date,create_time)<=7";

			String sql_zm = "select count(*) from article where key_word='" + keyword + "' and qgqx='正面'";
			String sql_fm = "select count(*) from article where key_word='" + keyword + "' and qgqx='负面'";
			String sql_zx = "select count(*) from article where key_word='" + keyword + "' and qgqx='中性'";

			hashMap.put("total", getOne(sql_total, "0"));
			hashMap.put("today", getOne(sql_today, "0"));
			hashMap.put("week", getOne(sql_week, "0"));
			hashMap.put("zm", getOne(sql_zm, "0"));
			hashMap.put("fm", getOne(sql_fm, "0"));
			hashMap.put("zx", getOne(sql_zx, "0"));

			return hashMap;
		}else {
			return getKeyWordDataAll();
		}


	}

	public static Map<String, String> getKeyWordDataAll() {

		HashMap<String, String> hashMap = new HashMap<String, String>();
		String sql_total = "select count(*) from article";
		String sql_today = "select count(*) from article where datediff(create_time,current_date)=0";
		String sql_week = "select count(*) from article  where datediff(current_date,create_time)<=7";


		hashMap.put("total", getOne(sql_total, "0"));
		hashMap.put("today", getOne(sql_today, "0"));
		hashMap.put("week", getOne(sql_week, "0"));


		return hashMap;

	}

	public static Map<String, Object> getWeekData(String keyword) {

		HashMap<String, Object> hashMap = new HashMap<String, Object>();
		Calendar cal = Calendar.getInstance();
		cal.setTime(new Date());
		cal.add(Calendar.DAY_OF_MONTH,1);
		for(int i=0;i<7;i++){
			cal.add(Calendar.DAY_OF_MONTH,-1);
			String day = DateFormat.getDateInstance(DateFormat.SHORT).format(new Date(cal.getTimeInMillis()));
			String sql_week = "select count(*) from article where key_word='" + keyword
					+ "' and datediff('"+day+"',create_time)=0";

			hashMap.put(day, getOne(sql_week, "0"));

		}

		return hashMap;

	}


	public static int  SaveKeyWord(String keyword) {
		int flag=0;
		String sql_count = "select count(*) from keywords where name='" + keyword + "'";
		//如果存在就更新，如果不存在就插入
		String res=getOne(sql_count,"0");
		if(Integer.parseInt(res)>0){
			executeQuery("update keywords set update_time=now() where name='" + keyword + "'");
		}else {
			executeQuery("insert into  keywords(name,create_time,update_time) values('" + keyword + "',now(),now())");
		}

		flag=1;
		return flag;

	}

	public static void main(String[] args) {
		getWeekData("孙莉");
	}

}
